﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;//添加命令空间，可以使用OleDbConnection等对象
using System.Data;//添加命令空间，可以使用dataset

public partial class student_selectCourse : System.Web.UI.Page
{
    public OleDbConnection myConnection;
    public static readonly string sConnectionString = System.Configuration.ConfigurationManager.AppSettings["SQLConnString"].ToString();
    protected void Page_Load(object sender, EventArgs e)
    {
        myConnection = new OleDbConnection(sConnectionString );
        myConnection.Open();
        if (Session["UserName"] == null && Session["UserNo"] == null)
            Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script type='text/javascript'>" +
                "alert('登录超时（或未登录），请您重新登陆！')；parent.location.herf='../login.asp'</script>", false);
        if (!IsPostBack)
        {
            bind1();
            bind2();
        }
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        bind1();

    }

    protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
    {
        string courseNo = GridView1.DataKeys[e.NewSelectedIndex][0].ToString();//获取所选择的行的索引，此处是获取GridView1.DataKeys（主键）的值cNo
        string courseName= GridView1.DataKeys[e.NewSelectedIndex][1].ToString();//获取所选择的行的索引，此处是获取GridView1.DataKeys（主键）的值cName
        string courseTerm = GridView1.DataKeys[e.NewSelectedIndex][2].ToString();//获取所选择的行的索引，此处是获取GridView1.DataKeys（主键）的值cTerm
        string coursePlace = GridView1.DataKeys[e.NewSelectedIndex][3].ToString();//获取所选择的行的索引，此处是获取GridView1.DataKeys（主键）的值cPlace
        string TeacherNo = GridView1.DataKeys[e.NewSelectedIndex][4].ToString();//获取所选择的行的索引，此处是获取GridView1.DataKeys（主键）的值tNo
        string TeacherName = GridView1.DataKeys[e.NewSelectedIndex][5].ToString();//获取所选择的行的索引，此处是获取GridView1.DataKeys（主键）的值cName
        //第二，在SelcorseTb选修课添加信息
        string myinsert="insert into SelcourseTb(sNo,sName,cNo,cTerm,cPlace,tNo,tName)values('"+Session ["UserNo"].ToString ()+"','"+Session["UserName"].ToString()+"','"+Session ["UserName"].ToString ()+
        "','"+courseNo +"','"+courseName +"','"+courseTerm+"','"+coursePlace +"','"+TeacherNo+"','"+TeacherName +"' )";//添加信息SQL语句
        OleDbCommand  mycmd=new OleDbCommand (myinsert,myConnection);
        mycmd.ExecuteScalar();
        //第二，在ScorceTb成绩表添加信息
        myinsert ="insert into sCoreTb(sNo,sName,cNo,cTerm,cPlace,tNo,tName)values('"+Session ["UserNo"].ToString ()+"','"+Session["UserName"].ToString()+
        "','"+courseNo +"','"+courseName +"','"+TeacherNo+"','"+TeacherName +"' )";//添加信息SQL语句
        mycmd.ExecuteScalar();
        /*同时刷新2张表*/
        bind1();//调用GridView1数据源绑定函数，选修
        bind2();//调用GridView1数据源绑定函数，取消
    }
    protected void GridView2_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView2 .PageIndex =e.NewPageIndex;//获取当前显示页的索引
        bind2();//调用GridView1数据源绑定函数，取消
    }
    protected void GridView2_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
    {
        /*必须先判断课程表是否已经结束（在成绩表ScourseTb表中已经给出分数，分数字段不为空），如果课程结束，那不允许取消课程*/
        string courseNo=GridView2 .DataKeys [e.NewSelectedIndex ][0].ToString ();//获取所选择的行的索引，此处是获取GridView2 .DataKeys（主键）的值cNo
        string mysql="select * from ScoreTb where sNo ='"+Session["UserNo"].ToString()+"'and cNo ='"+ courseNo+"' and sDegree is not null";
        OleDbCommand mycmd=new OleDbCommand (mysql,myConnection);
        OleDbDataReader myReader=mycmd.ExecuteReader();//执行SQL语句，存储在myReader
        if (myReader.Read())
            Page .ClientScript .RegisterStartupScript (this.GetType (),"","<script type='Text/javascript'>alert('本门课程已经结束，不允许取消课程!');</script>",false) ;
        else //不存在记录，可以取消课程，同时删除SelcourseTb和ScourseTb表的信息
        {
            string mystr="delect from SelcourseTb where sNo='"+Session ["UserNo"].ToString()+"'and cNo='"+courseNo +"'  ";//删除选修课程表SelcoreseTb
            mycmd =new OleDbCommand (mystr ,myConnection );
            mycmd .ExecuteNonQuery ();//执行SQL语句
            mystr ="delete from ScoreTb where sNo='"+Session ["UserNo"].ToString ()+"' and cNo='"+courseNo +"' ";//删除选修课程表ScoreTb
            mycmd =new OleDbCommand (mystr ,myConnection );
            mycmd .ExecuteNonQuery ();//执行SQL语句
            /*同时刷新2张表*/
            bind1();//调用GridView1数据绑定函数，选修
            bind2();//调用GridView2数据绑定函数，取消

    }
  }

    public void bind1()
    {
        string mysql="select * from CourseTb where cNo not in(select cNo from SelcourseTb where sNo='"+Session ["UserNo"].ToString ()+"')order by ID ";
        OleDbCommand mycmd=new OleDbCommand (mysql,myConnection);
        OleDbDataAdapter Adapter = new OleDbDataAdapter();//OleDbDataAdapter表示要对access数据源执行sql语句存储过程
        Adapter.SelectCommand =mycmd ;//获取或设置SQL语句存贮过程，用于选择数据源中的记录。（通俗来说，就是获取ScoreTb表的数据）
        DataSet myDS = new DataSet();//申明并初始化DataSet对象
        Adapter.Fill(myDS);//将数据填充到DataSet中。当OleDbDataAdapter填充到DataSet中时，他将为返回的数据创建适当的表和列
        if (myDS.Tables.Count > 0 && myDS.Tables[0].Rows.Count > 0)//判断myDS是不是空表，如果myDS至少存在1张表，并且表的行集合不为空
        {
            GridView1.DataSource = myDS;//装载数据源
            GridView1.DataKeyNames = new string[] { "sNo", "sName", "cNo", "cTerm", "cPlace", "tNo", "tName" };//设置主键，非常重要，关系到删除、编辑按钮的索引
            GridView1.DataBind();//绑定数据到显示列表
            Label1.Text = "";//装载数据源
        }
        else
        {
            Label1.Text = "无法查到相关数据！";
            GridView1.DataSource = null;//装载空数据源
            GridView1.DataBind();//绑定提示信息
        }
    }
    public void bind2()
    {
        string mysql="select * from SelcourseTb where sNo  sNo='"+Session ["UserNo"].ToString ()+"'order by ID ";
        OleDbCommand mycmd=new OleDbCommand (mysql,myConnection);
        OleDbDataAdapter Adapter = new OleDbDataAdapter();//OleDbDataAdapter表示要对access数据源执行sql语句存储过程
        Adapter.SelectCommand =mycmd ;//获取或设置SQL语句存贮过程，用于选择数据源中的记录。（通俗来说，就是获取ScoreTb表的数据）
        DataSet myDS = new DataSet();//申明并初始化DataSet对象
        Adapter.Fill(myDS);//将数据填充到DataSet中。当OleDbDataAdapter填充到DataSet中时，他将为返回的数据创建适当的表和列
        if (myDS.Tables.Count > 0 && myDS.Tables[0].Rows.Count > 0)//判断myDS是不是空表，如果myDS至少存在1张表，并且表的行集合不为空
        {
            GridView2.DataSource = myDS;//装载数据源
            GridView2.DataKeyNames = new string[] { "sNo", "sName", "cNo", "cTerm", "cPlace", "tNo", "tName" };//设置主键，非常重要，关系到删除、编辑按钮的索引
            GridView2.DataBind();//绑定数据到显示列表
            Label2.Text = "";//装载数据源
        }
        else
        {
            Label1.Text = "无法查到相关数据！";
            GridView1.DataSource = null;//装载空数据源
            GridView1.DataBind();//绑定提示信息
        }
    }
}
